package com.supinfo.bonjoursupinfo.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.supinfo.bonjoursupinfo.activities.MainTabFragmentActivity;
import com.supinfo.bonjoursupinfo.models.Place;

public class PlaceBDD {
	
	public static final String TABLE_NAME = "places";
	public static final String COL_ID = "_id";
	public static final int NUM_COL_ID = 0;
	public static final String COL_NAME = "name";
	public static final int NUM_COL_NAME = 1;
	public static final String COL_DESC = "description";
	public static final int NUM_COL_DESC= 2;
	public static final String COL_ADDRESS = "address";
	public static final int NUM_COL_ADDRESS = 3;
	public static final String COL_LATITUDE = "latitude";
	public static final int NUM_COL_LATITUDE = 4;
	public static final String COL_LONGITUDE = "longitude";
	public static final int NUM_COL_LONGITUDE = 5;
	public static final String COL_CATEGORY = "category";
	public static final int NUM_COL_CATEGORY = 6;
 
	public SQLiteDatabase bdd;
 
	private Database Database = MainTabFragmentActivity.database; 
	
	public void open(){
		bdd = Database.getWritableDatabase();
	}
 
	public void close(){
		bdd.close();
	}
 
	public SQLiteDatabase getBDD(){
		return bdd;
	}
	
	public long insertPlace(Place place){
		Place u = getPlaceWithId(place.getId());
		if(u == null){
			return bdd.insert(TABLE_NAME, null, putValues(place));
		}else{
			return updatePlace(place.getId(), place);
		}
	}
	
	public int updatePlace(long id, Place place){
		return bdd.update(TABLE_NAME, putValues(place), COL_ID + " = " +id, null);
	}
	
	public int removeBase(){
		return bdd.delete(TABLE_NAME, "1", null);
	}
	
	public int removePlaceWithID(long id){
		return bdd.delete(TABLE_NAME, COL_ID + " = " +id, null);
	}
	
//	public ArrayList<Place> getAllPlaces() {
//		Cursor c = bdd.query(TABLE_NAME, new String[] 
//				{COL_ID, COL_NAME, COL_DESC, COL_URL, COL_CREATIONDATE, COL_ISVALID},
//				null, null, null, null, COL_CREATIONDATE+" ASC");
//		return cursorToPlaces(c);
//	}
	
	public Cursor getAllPlaces() {
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_ADDRESS, COL_LATITUDE, COL_LONGITUDE, COL_CATEGORY},
				null, null, null, null, COL_ID+" DESC");
		return c;
	}
	
	public Place getPlaceWithName(String name){
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_ADDRESS, COL_LATITUDE, COL_LONGITUDE, COL_CATEGORY},
				COL_NAME + " LIKE \"" + name +"\"", null, null, null, COL_ID +" DESC");
		return cursorToPlace(c);
	}
	public Place getPlaceWithId(long id){
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_ADDRESS, COL_LATITUDE, COL_LONGITUDE, COL_CATEGORY}, 
				COL_ID + " LIKE \"" + id +"\"", null, null, null, COL_ID +" DESC");
		return cursorToPlace(c);
	}

	private Place cursorToPlace (Cursor c){
		if (c.getCount() == 0)return null;
		c.moveToFirst();
		Place place= new Place();
		place.setId(c.getLong(NUM_COL_ID));
		place.setName(c.getString(NUM_COL_NAME));
		place.setDescription(c.getString(NUM_COL_DESC));
		place.setAddress(c.getString(NUM_COL_ADDRESS));
		place.setLatitude(c.getFloat(NUM_COL_LATITUDE));
		place.setLongitude(c.getFloat(NUM_COL_LONGITUDE));
		place.setCategory(c.getString(NUM_COL_CATEGORY));
		c.close();
		return place;
	}
	
	public ArrayList<Place> cursorToPlaces(Cursor c){
		ArrayList<Place> all = new ArrayList<Place>();
		if(c.moveToFirst()){
			do {
				Place place= new Place();
				place.setId(c.getLong(NUM_COL_ID));
				place.setName(c.getString(NUM_COL_NAME));
				place.setDescription(c.getString(NUM_COL_DESC));
				place.setAddress(c.getString(c.getColumnIndex(COL_ADDRESS)));
				place.setLatitude(c.getFloat(c.getColumnIndex(COL_LATITUDE)));
				place.setLongitude(c.getFloat(c.getColumnIndex(COL_LONGITUDE)));
				place.setCategory(c.getString(c.getColumnIndex(COL_CATEGORY)));
				all.add(place);
			} while (c.moveToNext());
		}
		return all;
	}
	
	private ContentValues putValues(Place place){
		ContentValues values = new ContentValues();	
		values.put(COL_ID, place.getId());	
		values.put(COL_NAME, place.getName());
		values.put(COL_DESC, place.getDescription());
		values.put(COL_ADDRESS, place.getAddress());
		values.put(COL_LATITUDE, place.getLatitude());
		values.put(COL_LONGITUDE, place.getLongitude());
		values.put(COL_CATEGORY, place.getCategory());
		return values;
	}
}
